﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using QLDHS.DO;
namespace QLDHS.DAL
{
    class LUONG_DAL
    {
        OleDbConnection conn = Program.GetConnection();

        public DataSet loadAlllUONG()
        {
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                string sql = "select * from LUONG";
                OleDbDataAdapter adp = new OleDbDataAdapter(sql, conn);
                adp.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return ds;

        }

        public DataSet loadMaNV()
        {
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                string sql = "select MACB,HOTEN from HSNS";
                OleDbDataAdapter adp = new OleDbDataAdapter(sql, conn);
                adp.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return ds;

        }

        public DataTable loadALLnamebyMaNV(string manv)
        {
            DataTable ds = new DataTable();
            try
            {
                conn.Open();
                string sql = "select HOTEN  from HSNS where MACB="+manv+"";
                OleDbDataAdapter adp = new OleDbDataAdapter(sql, conn);
                adp.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return ds;

        }
        public int insertlUONG(LUONG_DO lg)
        {
            int kq = 0;
            try
            {
                    using (OleDbCommand cmd = conn.CreateCommand())
                    {
                        string text = "INSERT INTO LUONG VALUES (?,?,?,?,?,?)";
                        cmd.CommandText = text;
                        try
                        {
                            conn.Open();
                            cmd.Parameters.AddWithValue("@MALUONG", lg.Nvmaluong);
                            cmd.Parameters.AddWithValue("@MACB", lg.NvmaCB);
                            cmd.Parameters.AddWithValue("@TUNGAY", lg.Nvtungay);
                            cmd.Parameters.AddWithValue("@DENNGAY", lg.Nvdenngay);
                            cmd.Parameters.AddWithValue("@LUONGCOBAN", lg.Nvluongcoban);
                            cmd.Parameters.AddWithValue("@HESOLUONG", lg.Nvheso);
                            kq = cmd.ExecuteNonQuery();
                        }
                        catch (OleDbException ex)
                        {
                            throw ex;
                        }
                    }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return kq;
        }
        public int updateLuong(LUONG_DO lg)
        {
            int kq = 0;
            try
            {
                using (OleDbCommand cmd = conn.CreateCommand())
                {
                    string text = "update LUONG set MALUONG=?,MACB=?,TUNGAY=?,DENNGAY=?,LUONGCOBAN=?,HESOLUONG=?";
                    cmd.CommandText = text;
                    try
                    {
                        conn.Open();
                        cmd.Parameters.AddWithValue("@MALUONG", lg.Nvmaluong);
                        cmd.Parameters.AddWithValue("@MACB", lg.NvmaCB);
                        cmd.Parameters.AddWithValue("@TUNGAY", lg.Nvtungay);
                        cmd.Parameters.AddWithValue("@DENNGAY", lg.Nvdenngay);
                        cmd.Parameters.AddWithValue("@LUONGCOBAN", lg.Nvluongcoban);
                        cmd.Parameters.AddWithValue("@HESOLUONG", lg.Nvheso);
                        kq = cmd.ExecuteNonQuery();
                    }
                    catch (OleDbException ex)
                    {
                        throw ex;
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return kq;
        }
        public int delLUONG(string maluong)
        {
            int kqdel = 0;
            try
            {
                using (OleDbCommand cmd = conn.CreateCommand())
                {
                    string text = "delete from LUONG where MALUONG=?";
                    cmd.CommandText = text;
                    try
                    {
                        conn.Open();
                        cmd.Parameters.AddWithValue("@MALUONG", maluong);

                        kqdel = cmd.ExecuteNonQuery();
                    }
                    catch (OleDbException ex)
                    {
                        throw ex;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return kqdel;
        }
        public DataTable SearchByname(string name)
        {
            DataTable allcv = new DataTable();

            try
            {
                conn.Open();

                OleDbCommand cmd = new OleDbCommand("select * fom CHUCVU where TENCHUCVU like  '%" + name + " %'", conn);
                OleDbDataAdapter adp = new OleDbDataAdapter(cmd);

                adp.Fill(allcv);

            }
            finally
            {
                conn.Close();
            }


            return allcv;

        }

    }
}
